package com.tracking.application.DataBase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.followme.module.common.CommonModuleUtil;
import com.google.android.gms.maps.model.LatLng;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Kamil.O
 * Description: Helper save location to DB
 * Date: 05.12.13
 * Time: 18:33
 */
public class DbAdapter {
    public static final String DATABASE_NAME = "FollowMeDataBase";

    /*
    * Table Location
    * */
    public static final String KEY_L_DATE = "date";
    public static final String KEY_L_LAT = "lat";
    public static final String KEY_L_LNG = "lng";
    public static final String KEY_L_ID_ROUTE = "id_route";
    public static final String KEY_L_SPEED = "speed";
    public static final String KEY_L_DISTANCE = "distance";
    public static final String KEY_L_ALTITUDE = "altitude";

    /*
    * Table Road
    * */
    public static final String KEY_R_ID = "id";
    public static final String KEY_R_START_DATE = "start_date";
    public static final String KEY_R_END_DATE = "end_date";
    public static final String KEY_R_NAME = "name";
    public static final String KEY_R_TIME_STOP = "time_stop";

    /*
    * Table Statistics
    * */
    public static final String KEY_S_AVE_SPE_MOV = "average_spped_move";
    public static final String KEY_S_DIF_ALT = "difference_altitude";
    public static final String KEY_S_MAX_ALT = "max_altitude";
    public static final String KEY_S_MIN_ALT = "min_altitude";
    public static final String KEY_S_END_ALT = "end_altitude";
    public static final String KEY_S_STA_ALT = "start_altitude";
    public static final String KEY_S_MAX_SPE = "max_speed";
    public static final String KEY_S_TIM_MOV = "time_move";
    public static final String KEY_S_AVE_SPE = "average_speed";
    public static final String KEY_S_SPEED = "speed";
    public static final String KEY_S_TIME = "time";
    public static final String KEY_S_DISTANCE = "distance";
    public static final String KEY_S_ID_ROUTE = "id_road";
    public static final String KEY_S_ID = "id";


    public static final String LOCATION_TABLE = "Locations";
    public static final String ROUTE_TABLE = "Route";
    public static final String STATISTICS_TABLE = "Statistics";

    public static final int DATABASE_VERSION = 1;

    protected final Context context;

    protected DatabaseHelper DBHelper;
    protected SQLiteDatabase db;
    protected SimpleDateFormat formatter;

    public DbAdapter(Context ctx){
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
        formatter = new SimpleDateFormat("yyyy/MMM/dd HH:mm:ss");
    }

    private static class DatabaseHelper extends SQLiteOpenHelper{
        DatabaseHelper(Context context){
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) { }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
    }

    public DbAdapter open() throws SQLException{
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close(){
        DBHelper.close();
    }

    public long insertStatistics(String average_spped_move, String difference_altitude, String max_altitude, String min_altitude, String end_altitude, String start_altitude,
                                 String max_speed, String time_move, String average_speed, String speed, String time, String distance, String id_road){
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_S_AVE_SPE_MOV, average_spped_move);
        initialValues.put(KEY_S_DIF_ALT, difference_altitude);
        initialValues.put(KEY_S_MAX_ALT, max_altitude);
        initialValues.put(KEY_S_MIN_ALT, min_altitude);
        initialValues.put(KEY_S_END_ALT, end_altitude);
        initialValues.put(KEY_S_STA_ALT, start_altitude);
        initialValues.put(KEY_S_MAX_SPE, max_speed);
        initialValues.put(KEY_S_TIM_MOV, time_move);
        initialValues.put(KEY_S_AVE_SPE, average_speed);
        initialValues.put(KEY_S_SPEED, speed);
        initialValues.put(KEY_S_TIME, time);
        initialValues.put(KEY_S_DISTANCE, distance);
        initialValues.put(KEY_S_ID_ROUTE, id_road);

        return db.insert(LOCATION_TABLE, null, initialValues);
    }

    public long insertRoad(String name){
        Calendar currentDate = Calendar.getInstance();
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_R_START_DATE, formatter.format(currentDate.getTime()));
        initialValues.put(KEY_R_NAME, name);
        return db.insert(ROUTE_TABLE, null, initialValues);
    }

    public long updateEndDateRecording(String idRoute){
        Calendar currentDate = Calendar.getInstance();
        StringBuilder query = new StringBuilder();
        query.append(KEY_R_ID).append("=").append(idRoute);
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_R_END_DATE, formatter.format(currentDate.getTime()));
        return db.update(ROUTE_TABLE, initialValues, query.toString(), null);
    }

    public void deleteRoad(String idRoute) {
        db.delete(ROUTE_TABLE, KEY_R_ID+"="+idRoute, null);
        db.delete(LOCATION_TABLE, KEY_L_ID_ROUTE+"="+idRoute, null);
        db.delete(STATISTICS_TABLE, KEY_S_ID_ROUTE+"="+idRoute, null);
    }

    public long updateStopTime(String idRoad){
        Calendar currentDate = Calendar.getInstance();
        //TODO:
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_R_TIME_STOP, formatter.format(currentDate.getTime()));
        return db.insert(ROUTE_TABLE, null, initialValues);
    }

    public List<LatLng> getAllLatLong(String idRoute){
        List<LatLng> latLngs = new ArrayList<LatLng>();
        Cursor c = db.query(LOCATION_TABLE, new String[] {KEY_L_LAT, KEY_L_LNG}, KEY_L_ID_ROUTE+"="+idRoute, null, KEY_L_DATE, null, KEY_L_DATE+" asc");

        if (c != null ) {
            if  (c.moveToFirst()) {
                do {
                    Double lattitude = c.getDouble(c.getColumnIndex(KEY_L_LAT));
                    Double longtitude = c.getDouble(c.getColumnIndex(KEY_L_LNG));
                    latLngs.add(new LatLng(lattitude, longtitude));
                }while (c.moveToNext());
            }
        }
        c.close();

        return latLngs;
    }

    public long getTimeRoad(long idRoad) {
        Cursor c = db.query(ROUTE_TABLE, new String[] {KEY_R_START_DATE, KEY_R_END_DATE}, KEY_R_ID+"="+String.valueOf(idRoad), null, null, null, null);
        long difference = 0;
        if (c != null ) {
            if  (c.moveToFirst()) {
                do {
                    String startDate = c.getString(c.getColumnIndex(KEY_R_START_DATE));
                    String endDate = c.getString(c.getColumnIndex(KEY_R_END_DATE));

                    startDate = startDate.replace("/", "-").replace("gru", "12");
                    endDate = endDate.replace("/", "-").replace("gru", "12");

                    Date start = CommonModuleUtil.getDateFromString("yyyy-MM-dd HH:mm:ss", startDate);
                    Date end = CommonModuleUtil.getDateFromString("yyyy-MM-dd HH:mm:ss", endDate);

                    difference = end.getTime() - start.getTime();
                }while (c.moveToNext());
            }
        }
        c.close();

        return difference;
    }

    public List<Object> getAllRoute(){
        List<Object> list = new ArrayList<Object>();
        Cursor c = db.query(ROUTE_TABLE, new String[] {KEY_R_NAME, KEY_R_START_DATE, KEY_R_ID}, KEY_R_END_DATE+" IS NOT NULL", null, KEY_R_START_DATE, null, KEY_R_START_DATE+" desc");

        if (c != null ) {
            if  (c.moveToFirst()) {
                do {
                    String name = c.getString(c.getColumnIndex(KEY_R_NAME));
                    String date = c.getString(c.getColumnIndex(KEY_R_START_DATE));
                    String id = c.getString(c.getColumnIndex(KEY_R_ID));

                    list.add(name);
                    list.add(date);
                    list.add(id);
                }while (c.moveToNext());
            }
        }
        c.close();

        return list;
    }

    public long insertLocation(double lat, double lng, long idRoad, float spped,
                               float distance, double altitude){
        Calendar currentDate = Calendar.getInstance();
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_L_DATE, formatter.format(currentDate.getTime()));
        initialValues.put(KEY_L_LAT, lat);
        initialValues.put(KEY_L_LNG, lng);
        initialValues.put(KEY_L_ID_ROUTE, idRoad);
        initialValues.put(KEY_L_SPEED, spped);
        initialValues.put(KEY_L_DISTANCE, distance);
        initialValues.put(KEY_L_ALTITUDE, altitude);

        return db.insert(LOCATION_TABLE, null, initialValues);
    }

    public Map<String, List<Double>> getDataForGraph(String idRoute){
        Map<String, List<Double>> list = new HashMap<String, List<Double>>();
        Cursor c = db.query(LOCATION_TABLE, new String[] {KEY_L_ALTITUDE,
                KEY_L_SPEED, KEY_L_DISTANCE}, KEY_L_ID_ROUTE+"="+idRoute, null,
                KEY_L_DATE, null, KEY_L_DATE+" asc");

        List<Double> altitudeList = new ArrayList<Double>();
        List<Double> speedList = new ArrayList<Double>();
        List<Double> distanceList = new ArrayList<Double>();

        if (c != null ) {
            if  (c.moveToFirst()) {
                do {
                    altitudeList.add(c.getDouble(c.getColumnIndex(KEY_L_ALTITUDE)));
                    speedList.add(c.getDouble(c.getColumnIndex(KEY_L_SPEED)));
                    distanceList.add(c.getDouble(c.getColumnIndex(KEY_L_DISTANCE)));
                }while (c.moveToNext());
            }
        }
        c.close();

        list.put(KEY_L_ALTITUDE, altitudeList);
        list.put(KEY_L_SPEED, speedList);
        list.put(KEY_L_DISTANCE, distanceList);

        List<Double> temp = new ArrayList<Double>();
        temp.add(Double.valueOf(idRoute));
        list.put(KEY_L_ID_ROUTE, temp);

        return list;
    }

}
